package per.wei.diary.handler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import per.wei.diary.entity.MyDate;
import per.wei.diary.entity.MyDateTime;
import per.wei.diary.entity.MyDiary;
import per.wei.diary.entity.MyDiaryContent;

public class MyDiaryPersistBySqlite implements IMyDiaryPersist {

	private static final String Class_Name = "org.sqlite.JDBC";
    private static final String DB_URL = "jdbc:sqlite:D:\\MyDiaryDB.db";
    
    private static final String SQL_STRING = 
    		"insert into Content(`content`,`datetime`) values(?,?);"; 
    private static final String SQL_QUERY = 
    		"select (`id`,`content`,`datetime`) from Content where datetime >= ? and datetime <= ?;";
    private static final String SQL_MODIFY = 
    		"update Content set `content`= ?;";
    private static final String SQL_DELETE = 
    		"delete from Content where `id` = ?;";
    
    private Connection connection;
    
	public MyDiaryPersistBySqlite() {
		try {
			Class.forName(Class_Name);
			connection = DriverManager.getConnection(DB_URL);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	@Override
	public boolean insert(MyDiary myDiary) {
		try(PreparedStatement pstmt = connection.prepareStatement(SQL_STRING)){
			pstmt.setString(1, myDiary.getMyDiaryContent().getDiaryText());
			return pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean delete(int index) {
		try(PreparedStatement pstmt = connection.prepareStatement(SQL_DELETE)){
			pstmt.setInt(1, index);
			return pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public List<MyDiary> queryByDate(MyDateTime start, MyDateTime end) {
		List<MyDiary> myDiaryList = new LinkedList<MyDiary>();
		try(PreparedStatement pstmt = connection.prepareStatement(SQL_QUERY)){
			pstmt.setLong(1, start.toTimestamp());
			pstmt.setLong(2, end.toTimestamp());
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				MyDiary myDiary = new MyDiary();
				myDiary.setId(rs.getInt(1));
				MyDiaryContent myDiaryContent = new MyDiaryContent();
				myDiaryContent.setDiaryText(rs.getString(2));
				myDiary.setMyDiaryContent(myDiaryContent);
				myDiary.setMyDateTime(end);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean modify(int index, MyDiary myDiary) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void finalize() throws Throwable{
		if(connection != null ) {
			if(!connection.isClosed()) {
				connection.close();
				System.out.println("close sql connection");
			}
			connection = null;
		}
		System.out.println("sql finalize");
	}
}
